package com.guinea.core.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.guinea.core.orm.JdbcDao;
import com.guinea.core.tag.City;
import com.guinea.core.tag.DictEntry;
import com.guinea.core.tag.District;
import com.guinea.core.tag.Province;
import com.guinea.core.utils.SqlUtils;

public class CommonService {
	JdbcDao jdbcDao;

	public JdbcDao getJdbcDao() {
		return jdbcDao;
	}

	public void setJdbcDao(JdbcDao jdbcDao) {
		this.jdbcDao = jdbcDao;
	}

	public List getRsscs() {
		String sql = "select * from tdn_rssc";
		List result = getJdbcDao().getJdbcTemplate().query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						DictEntry rssc = new DictEntry();

						rssc.setCode(rs.getString("VRSSC_ID"));
						rssc.setName(rs.getString("VNAME"));
						return rssc;
					}
				});
		return result;
	}

	public List getProvinces() {
		String sql = "";
		sql += " SELECT   *";
		sql += "   FROM (SELECT vprovcode, vprovname, vprovshortname, vprovabb,";
		sql += "                visduchy, " + SqlUtils.toNumber("vdisporder")
				+ " o";
		sql += "           FROM tdn_province";
		sql += "          WHERE vprovcode <> '00' and vprovcode <> '60' and vprovcode <> '61' and vprovcode <> '43')";
		sql += " ORDER BY o";
		List result = getJdbcDao().getJdbcTemplate().query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						Province province = new Province();
						province.setAbbr(rs.getString("VPROVABB"));
						province.setIsDuchy(rs.getString("VISDUCHY"));
						province.setShortName(rs.getString("VPROVSHORTNAME"));

						province.setCode(rs.getString("VPROVCODE"));
						province.setName(rs.getString("VPROVNAME"));
						return province;
					}
				});
		return result;
	}

	public List getCities() {
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vcitycode, vcityname, vcityshortname, vprovcode,";
		sql += "                " + SqlUtils.toNumber("vdisporder") + " o";
		sql += "             FROM tdn_city";
		sql += "            WHERE vcitycode <> '0' AND vprovcode <> '00')";
		sql += " ORDER BY vprovcode, o";
		List result = getJdbcDao().getJdbcTemplate().query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						City city = new City();
						city.setShortName(rs.getString("VCITYSHORTNAME"));
						city.setCode(rs.getString("VCITYCODE"));
						city.setName(rs.getString("VCITYNAME"));
						city.setParentCode(rs.getString("VPROVCODE"));
						return city;
					}
				});
		return result;
	}

	public List getDistricts() {
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vdist_code, vdistname, vprovcode, vcitycode, vareacode,";
		sql += "                vzip_code, " + SqlUtils.toNumber("vdisporder")
				+ " o";
		sql += "             FROM tdn_district where vdist_code is not null)";
		sql += " ORDER BY vprovcode, vcitycode, o";
		List result = getJdbcDao().getJdbcTemplate().query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						District district = new District();
						district.setAreaCode(rs.getString("VAREACODE"));
						district.setAreaCode(rs.getString("VZIP_CODE"));
						district.setCode(rs.getString("VDIST_CODE"));
						district.setName(rs.getString("VDISTNAME"));
						district.setParentCode(rs.getString("VCITYCODE"));
						return district;
					}
				});
		return result;
	}

	public int getSequence(String seqname){
		String sql = "select " + seqname + ".nextval id from dual";
		return getJdbcDao().getJdbcTemplate().queryForInt(sql);
	}

	public void addSkodxUser(String loginName, String passwd, String employName) {
		int employId = getSequence("s_emply");
		String sql = "";
		sql += "Insert into AUTH_EMPLY";
		sql += "   (empid, grpid, empname, idtype, jobstate, titleid, crtuser, crttime)";
		sql += " Values";
		sql += "   ("+employId+", 0, '" + employName + "', '0', '1', 0, 0, to_char(sysdate,'yyyymmddhh24miss'))";
		System.out.println(sql);
		getJdbcDao().getJdbcTemplate().execute(sql);

		int userId = getSequence("s_user");
		sql = "";
		sql += "Insert into AUTH_USERMSG";
		sql += "   (USERID, LOGINNAME, PASSWD, WORKFLG, DATETIME, ONLNFLG, USERFLAG, EMPID, GRPID, LOGINID, CRTUSER, CRTTIME, VPDA_STATUS, VPDA_RESET, VPDA_FBDATE, VPDA_UPDATE)";
		sql += " Values";
		sql += "   ("+userId+", '"+loginName+"', '"+passwd+"', '0', to_char(sysdate,'yyyymmddhh24miss'), '0', '1', "+employId+", 0, 1, 0, to_char(sysdate,'yyyymmddhh24miss'), '1', '0', to_char(sysdate,'yyyymmddhh24miss'), to_char(sysdate,'yyyymmddhh24miss'))";
		System.out.println(sql);
		getJdbcDao().getJdbcTemplate().execute(sql);

		sql = "";
		sql += "Insert into AUTH_USERROLEREL";
		sql += "   (USERID, ROLEID, CRTUSER, CRTTIME)";
		sql += " Values";
		sql += "   ("+userId+", 920, 0, to_char(sysdate,'yyyymmddhh24miss'))";
		System.out.println(sql);
		getJdbcDao().getJdbcTemplate().execute(sql);
	}
	
	public void deleteSkodxUser(String loginName){
		String sql = "update auth_usermsg u set workflg = '1' where exists (select * from auth_userrolerel ur where ur.userid = u.userid and roleid = 920) and loginname = '"+loginName+"'";
		System.out.println(sql);
		getJdbcDao().getJdbcTemplate().execute(sql);
	}
	
	public void updateSkodxUser(String oldLoginName, String loginName, String passwd, String employName){
		String sql = "update auth_emply e set empname = '"+employName+"' where exists (select * from auth_usermsg u,auth_userrolerel ur where ur.userid = u.userid and e.empid = u.empid and roleid = 920 and u.workflg = '1' and u.loginname = '"+oldLoginName+"')";
		System.out.println(sql);
		getJdbcDao().getJdbcTemplate().execute(sql);
		
		sql = "update auth_usermsg u set loginname = '"+loginName+"',passwd='"+passwd+"' where exists (select * from auth_userrolerel ur where ur.userid = u.userid and roleid = 920) and loginname = '"+oldLoginName+"'";
		System.out.println(sql);
		getJdbcDao().getJdbcTemplate().execute(sql);
	}

	public boolean isSkodxUserExists(String loginName){
		String sql = "select count(1) from auth_usermsg u where exists (select * from auth_userrolerel ur where ur.userid = u.userid and roleid = 920) and loginname = '"+loginName+"' and workflg = '0'";
		System.out.println(sql);
		int amount = getJdbcDao().getJdbcTemplate().queryForInt(sql);
		return amount > 0;
	}
}
